#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb
import datetime
import time
import sys
import datetime

dbprefix="trade"
tbprefix="tradeorder"
host="127.0.0.1"
port=3306
user="test"
passwd="test"
offset=1200

dbconn = []
dbcursor = []

class QueryData(object):
	def __init__(self):
		orderId = None
		buyerUserId = None
		sellerUserId = None
		shipTime = None
		create_time = None
	#def __eq__(self, other):
		#return self.create_time == other.create_time and self.orderId == other.orderId

	def __cmp__(self, other):
		return cmp(self.create_time, other.create_time) or cmp(self.orderId, other.orderId)
	
	def __str__(self):
		return "[{0}, {1}, {2}, {3}, {4}]".format(self.orderId, self.buyerUserId, self.sellerUserId, self.shipTime, self.create_time)
	
def calcDbPrefix(orderId):
	return "{0}{1}".format("trade", (orderId % 10))

def calcTbPrefix(orderId):
	return "{0}{1}".format("tradeorder", (orderId / 10) % 10)

def createTable():
	for idx in xrange(0, 3):
		dbname = "{0}{1}".format(dbprefix, idx)
		sql = "{0}{1}{2}{3}".format("use ", dbname, ";CREATE TABLE `tradeorder", "` (`orderId` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',`buyerUserId` bigint(20) unsigned NOT NULL COMMENT '买家的userId',`sellerUserId` bigint(20) unsigned NOT NULL COMMENT '卖家的userId',`shipTime` int(11) unsigned DEFAULT '0' COMMENT '发货时间', `create_time` datetime, index `idx_ct`(`create_time`), PRIMARY KEY (`orderId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单信息表';")
		cursor = dbconn[idx].cursor()
		cursor.execute(sql)

def initConn():
	for idx in xrange(0, 3):
		dbname = "{0}{1}".format(dbprefix, idx)
		dbconn.append( MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname, port=port, charset='utf8mb4'))

def alterTable() :
	for idx in xrange(0, 3):
		dbname = "{0}{1}".format(dbprefix, idx)
		for idxx in xrange(0, 10):
			sql = "{0}{1}".format("use ", dbname, "; alter table `tradeorder", idxx, "` add index `idx_ct`(`create_time`)")
			cursor = dbconn[idx].cursor()
			cursor.execute(sql)

"""
查询第100页数据，一页数据10条，即limit 10 offset 1000
"""
def pagingSelect():
	limit = 10
	first_query_data = []
	table_num = 3 * 1
	page_offset = offset / table_num
	# 全局最小time
	time_all_min = sys.maxint
	# 存储每个分表的最大time值(dbn:tbn:tmv)
	time_tb_max = {}
	# 每个分表的第一个time值
	time_table_first = {}
	for idx in xrange(0, 3):
		dbname = "{0}{1}".format(dbprefix, idx)
		# 某个分库下所有分表的最大time值
		tb_max = {}
		# 记录第一次查询记录的第一个时间值
		table_first = {}
		sql = "select orderId, UNIX_TIMESTAMP(create_time) from trade{0}.`tradeorder` order by create_time limit {1}, {2}".format(idx,  page_offset, limit)
		cursor = dbconn[idx].cursor()
		cursor.execute(sql)
		rows = cursor.fetchall()
		table_first[0] = rows[0][1]
		tmin = rows[0][1]
		tmax = rows[len(rows)-1][1]
		tb_max[0] = tmax
		print rows
		if tmin < time_all_min:
			time_all_min = tmin
		time_table_first[idx] = table_first
		time_tb_max[idx] = tb_max
	# between all_time_min and table_time_max(最小时间的全局位置)
	total_offset = 0
	print time_tb_max
	print time_all_min
	# 第二次查询结果集合
	second_query_datas = []
	for dbtm in time_tb_max.items():
		dbidx = dbtm[0]
		table_time_first = time_table_first.get(dbidx)
		for tbtm in dbtm[1].items():
			tbidx = tbtm[0]
			tb_time_first = table_time_first.get(tbidx)
			tmmax = tbtm[1]
			sql = "select * from trade{0}.`tradeorder` where create_time between FROM_UNIXTIME({1}) and FROM_UNIXTIME({2}) order by create_time".format(dbidx, time_all_min, tmax)
			cursor = dbconn[idx].cursor()
			cursor.execute(sql)
			rows = cursor.fetchall()
			for row in rows:
				query_data = QueryData()
				query_data.orderId = row[0]
				query_data.buyerUserId = row[1]
				query_data.sellerUserId = row[2]
				query_data.shipTime = row[3]
				query_data.create_time = row[4]
				second_query_datas.append(query_data)	
			idxx = 0
			#if int(time.mktime(rows[0][4].timetuple())) != time_all_min:
				#idxx += 1
			for row in rows:
				time_ts = int(time.mktime(row[4].timetuple()))
				if time_ts == tb_time_first:
					break
				idxx += 1
			print idxx
			total_offset += page_offset - idxx
			print total_offset
	print total_offset
	second_query_datas = sorted(second_query_datas)	
	result_idx = offset - total_offset
	# 最终数据
	result_data = second_query_datas[result_idx: result_idx + 10]
	#for t in second_query_datas:
	for t in result_data:
		print t

if __name__ == '__main__':
	initConn()
	pagingSelect()
	#alterTable()
	#createTable()

	# 模拟生成数据
	"""
	buyerUserId = 123
	sellerUserId = 123
	shipTime = 123
	import random
	st = int(time.mktime(datetime.datetime.now().timetuple()))
	tmp = st	
	flag = False
	for i in xrange(0, 6000):
		if i % 2000 == 0:
			if flag:
				st = tmp + 2
			else:
				st = tmp - 3
		orderId = '{:0<12}'.format(random.randint(000000000000, 999999999999))
		db = long(orderId) % 3
		sql = "INSERT INTO `trade{0}`.`tradeorder` (`orderId`, `buyerUserId`, `sellerUserId`, `shipTime`, `create_time`) VALUES ({1}, {2}, {3}, {4}, FROM_UNIXTIME({5}))".format(db, orderId, buyerUserId, sellerUserId, shipTime, st)
		if flag:
			st += 2
			flag = False
		else:
			st -= 3
			flag = True
		cursor = dbconn[db].cursor()
		cursor.execute(sql)
		dbconn[db].commit()
	"""
